package kr.hkit.summary_java.std.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.hkit.myframework.db.ConnectionFactory;
import kr.hkit.myframework.db.DbUtil;
import kr.hkit.summary_java.std.dto.Student;

public class StudentDao {
	private Connection connection = ConnectionFactory.getConnection();
	private PreparedStatement pstmt;

	public void insertItem(Student item) throws SQLException {
		String sql = "insert into student(stdnum, stdname) values(?, ?)";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getStdNum());
		pstmt.setString(2, item.getStdName());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}
	
	public void updateItem(Student item) throws SQLException {
		String sql = "update student set kor=?, eng=?, math=? where stdnum = ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getKor());
		pstmt.setInt(2, item.getEng());
		pstmt.setInt(3, item.getMath());
		pstmt.setInt(4, item.getStdNum());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	public void deleteAllItem() throws SQLException {
		String sql = "delete from student";
		pstmt = connection.prepareStatement(sql);
		pstmt.executeUpdate();
		DbUtil.close(pstmt);
	}

	public ArrayList<Student> listItems() throws SQLException {
		ArrayList<Student> list = new ArrayList<>();
		String sql = "select * from student";
		pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			list.add(new Student(rs.getInt("stdnum"), rs.getString("stdname"), rs.getInt("kor"), rs.getInt("eng"), rs.getInt("math")));
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return list;
	}
	
	public Student searchItem(Student item) throws SQLException {
		Student std = null;
		String sql = "select * from Student where stdnum = ?";
		pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getStdNum());
		ResultSet rs = pstmt.executeQuery();
		
		if (rs.next()) {
			std = new Student();
			std.setStdNum(rs.getInt(1));
			std.setStdName(rs.getString(2));
		}
		return std;
	}
}
